package org.shoukaiseki.inittable.handle.database;

import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.codec.digest.DigestUtils;
import org.shoukaiseki.common.utils.StringUtils;
import org.shoukaiseki.inittable.domain.*;
import org.shoukaiseki.inittable.exception.InitTableException;
import org.shoukaiseki.inittable.logger.InitTableLoggers;
import org.slf4j.Logger;
import org.springframework.beans.BeanUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


public class InitTableHandleMysql extends BaseInitTableHandle{

    public static final Logger logger = InitTableLoggers.DEFAULT_LOGGER;

    private int version=1;


    public InitTableHandleMysql(Connection conn, TableInfo tableInfo){
        super(conn,tableInfo);
    }

    @Override
    public SksInitTableVersion selectTableVersion(String tableName) throws SQLException {
        String sql=StringUtils.format("select * from {} where name='{}'",sksInitTableVersion,tableName.replaceAll("`",""));
        ResultSet resultSet = selectBySql(sql);
        SksInitTableVersion sksInitTableVersion = null;
        if( resultSet.next()){
            sksInitTableVersion = new SksInitTableVersion();

//            sksInitTableVersion.setName(resultSet.getString("name"));
//            sksInitTableVersion.setComment(resultSet.getString("comment"));
//            sksInitTableVersion.setDescription(resultSet.getString("description"));
//            sksInitTableVersion.setRemark(resultSet.getString("remark"));
//            sksInitTableVersion.setCurrentVersion(resultSet.getInt("current_version"));
//            sksInitTableVersion.setJavaVersion(resultSet.getInt("java_version"));
//            sksInitTableVersion.setPrimaryKey(resultSet.getString("primary_key"));
//            sksInitTableVersion.setDefaultCharset(resultSet.getString("default_charset"));
//            sksInitTableVersion.setEngine(resultSet.getString("engine"));
//            sksInitTableVersion.setAutoIncrement(resultSet.getString("auto_increment"));
//            if(resultSet.getTimestamp("create_time")!=null){
//            sksInitTableVersion.setCreateTime(resultSet.getTimestamp("create_time").getTime());
//            }
//            if(resultSet.getTimestamp("update_time")!=null){
//                sksInitTableVersion.setUpdateTime(resultSet.getTimestamp("update_time").getTime());
//            }
            sksInitTableVersion=sksInitTableVersionMapper.selectSksInitTableVersionById(tableName);
        }
        closeStatement();
        return sksInitTableVersion;
    }

    @Override
    public List<SksInitTableVersion> selectTableVersionList(SksInitTableVersionQO sksInitTableVersionQO) {
        return sksInitTableVersionMapper.lookupSksInitTableVersionList(sksInitTableVersionQO);
    }


    @Override
    public void updateCurrentMd5(){
        String s = JSONObject.toJSONString(tableInfo);
        String md5 = DigestUtils.md5Hex(s);
        SksInitTableVersion sksInitTableVersion = new SksInitTableVersion();
        sksInitTableVersion.setCurrentMd5(md5);
        sksInitTableVersion.setName(tableName);
        updateTableVersion(sksInitTableVersion);
    }

    @Override
    public void updateLastMd5(){
        String s = JSONObject.toJSONString(tableInfo);
        String md5 = DigestUtils.md5Hex(s);
        SksInitTableVersion sksInitTableVersion = new SksInitTableVersion();
        sksInitTableVersion.setLastMd5(md5);
        sksInitTableVersion.setName(tableName);
        updateTableVersion(sksInitTableVersion);
    }

    @Override
    public void insertTableVersion(SksInitTableVersion sksInitTableVersion) {
        try {
            SksInitTableVersion tmp = selectTableVersion(sksInitTableVersion.getName());
            if(tmp==null){
                sksInitTableVersionMapper.insertSksInitTableVersion(sksInitTableVersion);
            }else{
                sksInitTableVersionMapper.updateSksInitTableVersion(sksInitTableVersion);
            }
        } catch (Throwable t) {
            throw new InitTableException("insertTableVersion"+sksInitTableVersion+"失败!",t);
        }
    }

    @Override
    public void updateTableVersion(SksInitTableVersion sksInitTableVersion) {
        sksInitTableVersionMapper.updateSksInitTableVersion(sksInitTableVersion);
    }

    @Override
    public void insertTableInfoToVersion(TableInfo tableInfo) {
        SksInitTableVersion sksInitTableVersion=new SksInitTableVersion();
        BeanUtils.copyProperties(tableInfo,sksInitTableVersion);
        sksInitTableVersion.setJavaVersion(tableInfo.getVersion().intValue());
//        sksInitTableVersion.setEngine(tableInfo.getEngine());
//        sksInitTableVersion.setName(tableInfo.getName());
//        sksInitTableVersion.setComment(tableInfo.getComment());
//        sksInitTableVersion.setPrimaryKey(tableInfo.getPrimaryKey());
        String str=null;
        if(tableInfo.getTableIndexInfoList()!=null){
            str= JSONObject.toJSONString(tableInfo.getTableIndexInfoList());
            sksInitTableVersion.setIndexJson(str);
        }
        if(tableInfo.getTableTriggerInfoList()!=null){
            str= JSONObject.toJSONString(tableInfo.getTableTriggerInfoList());
            sksInitTableVersion.setTriggerJson(str);
        }
        if (tableInfo.getTableConstraintInfoList()!=null) {
            str= JSONObject.toJSONString(tableInfo.getTableConstraintInfoList());
            sksInitTableVersion.setConstraintJson(str);
        }
        insertTableVersion(sksInitTableVersion);
    }

    @Override
    public void buildSksInitTableVersion(boolean reset) {
        try {
            if(reset){
                String sql=StringUtils.format("drop table if exists {}",sksInitTableVersion);
                logger.debug("sql={}",sql);
                createStatementExecuteUpdate(sql);
                return;
            }
            if (hasTable(sksInitTableVersion)) {
                SksInitTableVersion sksInitTableVersion = selectTableVersion(this.sksInitTableVersion);
                if (sksInitTableVersion.getCurrentVersion()!=version) {
                    String sql=StringUtils.format("drop table if exists {}",sksInitTableVersion);
                    createStatementExecuteUpdate(sql);
                    buildSksInitTableVersion(false);
                    return;
                }
            }else{
                String tmp="create table {}\n" +
                        "(\n" +
                        "  name          varchar(128)      not null     comment '表名称',\n" +
                        "  bean_source    varchar(512)      not null     comment '表资源路径',\n" +
                        "  comment        varchar(512)    default ''                 comment '表注释',\n" +
                        "  description        varchar(512)    default ''                 comment '表描述',\n" +
                        "  current_version  int(12)    default '-1'           not null comment '当前版本',\n" +
                        "  java_version          int(12)         default '0'      not null         comment '要更新的版本',\n" +
                        "  primary_key          varchar(128)  default ''   not null    comment '主键',\n" +
                        "  default_charset          varchar(128)  default ''   not null    comment '默认字符集',\n" +
                        "  engine          varchar(128)  default ''   not null    comment '数据库引擎',\n" +
                        "  auto_increment  bigint(20)  default 1 not null comment '自动生成初始值',\n" +
                        "  create_time   datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',\n" +
                        "  update_time   datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',\n" +
                        "  remark         varchar(500)    default null               comment '备注',\n" +
                        "  type  int(20)  default 0 not null comment '类型:0:table;1:视图(暂不支持)',\n" +
                        "  execute_sql    varchar(2000)    default ''  not null      comment '执行的sql',\n" +
                        "  index_json    varchar(2000)    default ''  not null      comment '索引',\n" +
                        "  trigger_json    varchar(2000)    default ''  not null      comment '触发器',\n" +
                        "  constraint_json    varchar(2000)    default ''  not null      comment '限制',\n" +
                        "  execute_index_sql    varchar(2000)    default ''  not null      comment '执行的索引sql',\n" +
                        "  execute_trigger_sql    varchar(2000)    default ''  not null      comment '执行的触发器sql',\n" +
                        "  last_md5    varchar(64)    default ''  not null      comment '最后一次更新的sql语句md5',\n" +
                        "  current_md5    varchar(64)    default ''  not null      comment '当前更新的sql语句md5',\n" +
                        "  primary key (name)\n" +
                        ") ";
                tmp=StringUtils.format(tmp,sksInitTableVersion);
                StringBuilder sql=new StringBuilder(tmp);
                StringBuilder insertAttrs=new StringBuilder("insert into sks_init_table_version(name,bean_source, comment,current_version, java_version,primary_key ");
                StringBuilder insertValues=new StringBuilder(StringUtils.format("value('{}','base-init-table.jar', '初始化表版本管理',{},{} ,'name'",sksInitTableVersion,version,version));

                if(getTableBuildProperties().getEngine()!=null){
                    sql.append(StringUtils.format(" engine={}",getTableBuildProperties().getEngine()));
                    insertAttrs.append(",engine");
                    insertValues.append(StringUtils.format(",'{}'",getTableBuildProperties().getEngine()));
                }
                if(getTableBuildProperties().getDefaultCharset()!=null){
                    sql.append(StringUtils.format(" default charset = {}",getTableBuildProperties().getDefaultCharset()));
                    insertAttrs.append(",default_charset");
                    insertValues.append(StringUtils.format(",'{}'",getTableBuildProperties().getDefaultCharset()));
                }
                if(getTableBuildProperties().getAutoIncrement()!=null){
                    sql.append(StringUtils.format(" auto_increment = {}",getTableBuildProperties().getAutoIncrement()));
                    insertAttrs.append(",auto_increment");
                    insertValues.append(StringUtils.format(",'{}'",getTableBuildProperties().getAutoIncrement()));

                }
                sql.append(" comment = '初始化表版本管理';");
                logger.debug("sql={}",sql.toString());
                createStatementExecuteUpdate(sql.toString());
                insertAttrs.append(")  ").append(insertValues).append(")");
                logger.debug("insertSql={}",insertAttrs.toString());
                createStatementExecuteUpdate(insertAttrs.toString());
            }


        } catch (Throwable t) {
            throw new InitTableException("初始化"+sksInitTableVersion+"失败!",t);
        }
    }

    @Override
    public void buildColumn(){
        try{
            //为了保证数据安全性,table以及列不允许删除
            // 有需求则需要使用数据库管理工具删除
            if (hasTable(this.tableName)) {
                modifyTable();
            }else{
                createTable();
            }

            SksInitTableVersion sksInitTableVersion = new SksInitTableVersion();
            sksInitTableVersion.setCurrentVersion(tableInfo.getVersion());
            sksInitTableVersion.setName(tableName);
            updateTableVersion(sksInitTableVersion);
        }catch (Throwable t){
            logger.error("",t);
            throw new InitTableException("出错了",t);
        }
    }

    @Override
    public void buildIndex(int initType) {
        List<TableIndexInfo> tableIndexInfoList = tableInfo.getTableIndexInfoList();
        if (tableIndexInfoList!=null) {
            for (TableIndexInfo tableIndexInfo : tableIndexInfoList) {
                String sql=null;
                try{
                    String indexName = tableIndexInfo.getIndexName();


                    boolean b = hasIndex(indexName);

                    //重建索引,存在则删除索引
                    if((initType&0b10)>0&&b){
                        dropIndex(indexName);
                        b=false;
                    }
//                        b=false;
                    if (!b) {
                        StringBuilder sb=new StringBuilder();
                        if (tableIndexInfo.isUniqueIndex()) {
                            sb.append("create  unique index ");
                        }else{
                            sb.append("create  index ");
                        }
                        sb.append(indexName).append(" on ");
                        sb.append(tableName);
                        sb.append("(").append(tableIndexInfo.getIndexColumns()).append(")");
                        sql = sb.toString();
                        addExecuteSql(sql);
                        createStatementExecuteUpdate(sql);
                        closeStatement();
                    }
                }catch (Throwable t){
                    addErrorIndexSql(sql);
                    logger.error("",t);
                }

            }
        }
    }

    @Override
    public void buildConstraint(int initType) {
        List<TableConstraintInfo> tableConstraintInfoList = tableInfo.getTableConstraintInfoList();
        if (tableConstraintInfoList!=null) {
            for (TableConstraintInfo tableConstraintInfo : tableConstraintInfoList) {
                String sql=null;
                try {
                    String constraintName = tableConstraintInfo.getConstraintName();

                    boolean b = hasConstraint(constraintName);
                    //重建限制,存在则删除限制
                    if((initType&0b1000)>0&&b){
                        dropConstraintName(constraintName);
                        b=false;
                    }
                    if (!b) {
                        sql = tableConstraintInfo.buildAddSql(tableName);
                        addExecuteSql(sql);
                        createStatementExecuteUpdate(sql);
                        closeStatement();
                    }
                } catch(Throwable t) {
                    addErrorIndexSql(sql);
                    logger.error("",t);
                }

            }
        }
    }



    @Override
    public void buildTrigger(int initType) {
        List<TableTriggerInfo> triggerInfoList = tableInfo.getTableTriggerInfoList();
        if (triggerInfoList!=null) {
            for (TableTriggerInfo tableTriggerInfo : triggerInfoList) {
                String sql=null;
                try{
                    String triggerName = tableTriggerInfo.getTriggerName();
                    boolean b = hasTrigger(triggerName);
                    if((initType&0b100)>0){
                        //重建触发器
                        dropTriggerIfExists(triggerName);
                        b=false;
                    }
//                    b=false;
                    if (!b) {
                        sql=tableTriggerInfo.getTriggerSql();
                        addExecuteSql(sql);
                        createStatementExecuteUpdate(sql);
                        closeStatement();
                    }
                }catch (Throwable t){
                    addErrorTriggerSql(sql);
                    logger.error("",t);
                }

            }
        }
    }


    private void  createTable() throws SQLException {
        StringBuilder sb=new StringBuilder("create table ").append(tableName).append("(");
        List<TableColumnInfo> columnInfoList = tableInfo.getColumnInfoList();
        for (int i = 0; i < columnInfoList.size(); i++) {
            TableColumnInfo tableColumnInfo = columnInfoList.get(i);
            sb.append("").append(tableColumnInfo.getColumnName()).append(" ").append(tableColumnInfo.getColumnDetail());
            if (tableColumnInfo.isPrimaryKey()) {
                sb.append(" primary key ");
            }
            sb.append(",");
        }
        if(StringUtils.isEmpty(tableInfo.getPrimaryKey())){
            if (sb.lastIndexOf(",")==sb.length()-1) {
                sb.deleteCharAt(sb.length()-1);
            }
        }else{
            sb.append(tableInfo.getPrimaryKey());
        }
        sb.append(") engine=").append(tableInfo.getEngine());
        sb.append(" default charset =").append(tableInfo.getDefaultCharset());
        sb.append(" auto_increment =").append(tableInfo.getAutoIncrement());
        sb.append(" comment '").append(tableInfo.getComment()).append("'");
        String sql = sb.toString();
        addExecuteSql(sql);
        createStatementExecuteUpdate(sql);
        closeStatement();

        List<String> createTableInitDataList = tableInfo.getCreateTableInitDataList();
        for (String sqlTmp : createTableInitDataList) {
            if (StringUtils.isNotEmpty(sqlTmp)) {
                createStatementExecuteUpdate(sqlTmp);
            }
        }

    }

    private void modifyTable() throws SQLException {
        String sql=StringUtils.format("alter table {} comment '{}'", tableName,tableInfo.getComment());
        addExecuteSql(sql);
        createStatementExecuteUpdate(sql);
        closeStatement();
        String previousColumnName=null;
        List<TableColumnInfo> columnInfoList = tableInfo.getColumnInfoList();
        for (TableColumnInfo tableColumnInfo : columnInfoList) {
            String columnName = tableColumnInfo.getColumnName();
            if (hasColumn(columnName)) {
                sql=StringUtils.format("alter table {} modify {} {}", tableName,columnName,tableColumnInfo.getColumnDetail());
            }else{
                sql=StringUtils.format("alter table {} add {} {}",tableName, columnName,tableColumnInfo.getColumnDetail());
            }
            if (previousColumnName!=null) {
                sql=sql+" after "+previousColumnName;
            }
            addExecuteSql(sql);
            createStatementExecuteUpdate(sql);
            closeStatement();
            previousColumnName=columnName;

        }

    }


    public void dropTriggerIfExists(String triggerName) throws SQLException {

        String sql= StringUtils.format("drop trigger if exists  `{}`' "
                ,triggerName.replaceAll("`",""));
        addExecuteSql(sql);
        createStatementExecuteUpdate(sql);
        closeStatement();
    }

    public boolean hasTrigger(String triggerName) throws SQLException {
        String sql= StringUtils.format("select 1 from information_schema.TRIGGERS where TRIGGER_SCHEMA=database() and lower(TRIGGER_NAME)=lower('{}') "
                ,triggerName.replaceAll("`",""));
        ResultSet resultSet = selectBySql(sql);
        boolean next = resultSet.next();
        closeStatement();
        return next;
    }

    /**
     * 删除限制
     * @param constraintName
     * @throws SQLException
     */
    public void dropConstraintName(String constraintName) throws SQLException {
        String sql= StringUtils.format("alter table `{}` drop constraint `{}`"
                ,tableName.replaceAll("`",""),constraintName.replaceAll("`",""));
        addExecuteSql(sql);
        createStatementExecuteUpdate(sql);
        closeStatement();
    }

    /**
     * 限制是否存在
     * @param constraintName
     * @return
     * @throws SQLException
     */
    public boolean hasConstraint(String constraintName) throws SQLException {
        String sql= StringUtils.format("select 1 from information_schema.table_constraints where lower(TABLE_NAME)=lower('{}') and lower(CONSTRAINT_NAME)=lower('{}') "
                ,tableName.replaceAll("`",""),constraintName.replaceAll("`",""));
        ResultSet resultSet = selectBySql(sql);
        boolean next = resultSet.next();
        closeStatement();
        return next;
    }

    public void dropIndex(String indexName)throws SQLException{
        String sql= StringUtils.format("drop index `{}` on `{}`"
                ,indexName.replaceAll("`",""),tableName.replaceAll("`",""));
        addExecuteSql(sql);
        createStatementExecuteUpdate(sql);
        closeStatement();
    }

    public boolean hasIndex(String indexName) throws SQLException {
        String sql= StringUtils.format("select 1 from information_schema.statistics where lower(TABLE_NAME)=lower('{}') " +
                        "and lower(INDEX_NAME)=lower('{}')"
                ,tableName.replaceAll("`",""),indexName.replaceAll("`",""));
        ResultSet resultSet = selectBySql(sql);
        boolean next = resultSet.next();
        closeStatement();
        return next;
    }

    public boolean hasColumn(String columnName) throws SQLException {
        String sql= StringUtils.format("select 1 from information_schema.columns where upper(TABLE_NAME)=upper('{}')  " +
                "and upper(COLUMN_NAME)=upper('{}')",tableName.replaceAll("`",""),columnName.replaceAll("`",""));
        ResultSet resultSet = selectBySql(sql);
        boolean next = resultSet.next();
        closeStatement();
        return next;
    }

    public boolean hasTable(String tableName) throws SQLException {
        String sql= StringUtils.format("select 1 from information_schema.tables where upper(TABLE_NAME)=upper('{}')",tableName.replaceAll("`",""));
        ResultSet resultSet = selectBySql(sql);
        boolean next = resultSet.next();
        closeStatement();
        return next;
    }




}
